<?php
error_reporting(E_ALL);
$relPath = "./pinc/";
require_once $relPath."dpinit.php";
require_once $relPath."dp_main.inc";

$qtitle         = Arg("qtitle");
$qauthor        = Arg("qauthor");
$qpm            = Arg("qpm");
$qlang          = ArgArray("qlang");
$qgenre         = Arg("qgenre");
$qprojectid     = Arg("qprojectid");
$qroundid       = Arg("qroundid");
$qstatus        = Arg("qstatus", "avail");
$sort           = Arg("sort");
$desc           = ArgBoolean("desc");
$qtask          = Arg("qtask");

define("MAX_ROWS_PER_PAGE", 200);

$pagerows       = Arg("pagerows", MAX_ROWS_PER_PAGE);
$rownum         = Arg("rownum", 1);

$fmt = "<span id='%s' class='lnk'>%s</span>\n";
$titleCaption   = sprintf($fmt, "lktitle",    _("Title"));
$authorCaption  = sprintf($fmt, "lkauthor",   _("Author"));
$langCaption    = sprintf($fmt, "lklang",     _("Lang"));
$projidCaption  = sprintf($fmt, "lkprojid",   _("Project"));
$genreCaption   = sprintf($fmt, "lkgenre",    _("Genre"));
$pmCaption      = sprintf($fmt, "lkpm",       _("Proj Mgr"));
$diffCaption    = sprintf($fmt, "lkdiff",     _("Task"));
$roundCaption   = sprintf($fmt, "lkround",    _("Round"));

$tbl = new DpTable();
$tbl->AddColumn("<".$titleCaption,  "title",    "title_link");
$tbl->AddColumn("<".$authorCaption, "author");
$tbl->AddColumn("^".$langCaption,   null,       "all_langs");
$tbl->AddColumn("^".$genreCaption,  "genre");
$tbl->AddColumn(_("^avail-out-done<br />(bad-total)"), 
                                        null, "page_counts");
$tbl->AddColumn("<".$pmCaption,     "project_manager", "pmlink");
$tbl->AddColumn(_("^PP/PPV"),   "checkedoutby", "coblink");
$tbl->AddColumn("^".$diffCaption,   null,       "difficulty_cell");
$tbl->AddColumn("^".$roundCaption,  null, "roundid");
$tbl->AddColumn(_("^Words"),    null, "words_link");
$tbl->AddColumn(_("^Edit"),     null, "edit_link");
$tbl->AddColumn("^".$projidCaption, "projectid");

$awhere = array();

if($qtitle) {
    $qqtitle    = mysql_real_escape_string($qtitle);
    $words      = preg_split("/\s+/", $qqtitle);
    $qsql       = "(p.nameofwork LIKE '%"
        . implode("%'\nor p.nameofwork LIKE '%", $words)
        . "%')";
    $awhere []  = $qsql;
}

if($qauthor) {
    $qqauthor   = mysql_real_escape_string($qauthor);
    $words      = preg_split("/\s+/", $qqauthor);
    $qsql       = "(p.authorsname LIKE '%"
        . implode("%'\nor p.authorsname LIKE '%", $words)
        . "%')";
    $awhere []  = $qsql;
}




if($qtask) {
    $a = array();
    foreach($qtask as $q) {
        $a[] ="difficulty LIKE '{$q}%'";
    }
        
    if(count($a) > 1) {
        $awhere [] = "(".implode(" OR ", $a).")";
    }
    else if(count($a) == 1) {
        $awhere [] = $a[0];
    }
}


if($qroundid) {
    $a = array();
    foreach($qroundid as $q) {
        $a[] ="p.state LIKE '{$q}%'";
    }
        
    if(count($a) > 1) {
        $awhere [] = "(".implode(" OR ", $a).")";
    }
    else if(count($a) == 1) {
        $awhere [] = $a[0];
    }
}

if(is_array($qpm) && count($qpm) > 0) {
    $a = array();
    foreach($qpm as $q) {
        $a[] ="p.username LIKE '{$q}'";
    }
        
    if(count($a) > 1) {
        $awhere [] = "(".implode(" OR ", $a).")";
    }
    else if(count($a) == 1) {
        $awhere [] = $a[0];
    }
}

if($qgenre) {
    $a = array();
    foreach($qgenre as $q) {
        $a[] ="genre LIKE '%{$q}%'";
    }

    if(count($a) > 1) {
        $awhere [] = "(".implode(" OR ", $a).")";
    }
    else if(count($a) == 1) {
        $awhere [] = $a[0];
    }
}

if($qlang) {
    $a = array();
    foreach($qlang as $q) {
        $a[] ="CONCAT(language, ' ', 
            COALESCE(other_languages, '')) LIKE '%{$q}%'";
    }
        
    if(count($a) > 1) {
        $awhere [] = "(".implode(" OR ", $a).")";
    }
    else if(count($a) == 1) {
        $awhere [] = $a[0];
    }
}

if($qprojectid) {
    $qqid = mysql_real_escape_string($qprojectid);
    $ids = preg_split("/\s+/", $qqid);
    $qsql = "(p.projectid LIKE '%"
        . implode("%'\nor p.projectid LIKE '%", $ids)
        . "%')";
    $awhere [] = $qsql;
}

if($qstatus) {
    switch($qstatus) {
        default:
            break;

        case "unavail":
            $awhere[] = "p.state LIKE '%proj_unavail'";
            break;

        case "avail":
            $awhere[] = "p.state LIKE '%proj_avail'";
            break;
    }
}

$where = implode("\nAND ", $awhere);
$sql = project_search_view_sql($where);

switch($sort) {
    case "lktitle":
        $orderby = "p.nameofwork";
        break;

    case "lkauthor":
        $orderby = "p.authorsname";
        break;

    case "lkgenre":
        $orderby = "p.genre";
        break;

    case "lkpm":
        $orderby = "p.username";
        break;

    case "lkprojid":
        $orderby = "p.projectid";
        break;

    case "lklang":
        $orderby = "p.language";
        break;

    case "lkdiff":
        $orderby = "p.difficulty";
        break;

    case "lkround":
        // use surrogate derived field
        $orderby = "round_index";
        break;

    default:
        $orderby = "p.nameofwork";
        break;
}

$sql .= " ORDER BY {$orderby}";
if($desc) {
    $sql .= " DESC";
}

$all_rows = $dpdb->SqlRows($sql);

$nprojects = count($all_rows);

$title = _("Search projects");

$args = array("js_file" => $js_url."/search.js");
theme_header(_("Search projects"), false, $args);

// get array of langcode => langname
$optlanguages = $Context->ActiveLanguages();

$language_picker = "
    <select name='qlang' id='qlang[]' 
                            size='8' multiple='multiple'>
        <option value=''> </option>\n";
foreach($optlanguages as $lang => $name) {
    $language_picker .= "
        <option value='{$lang}'"
        .(in_array($lang, $qlang) ? " selected='selected'" : "")
        .">{$name}</option>";
}
$language_picker .= "
    </select>\n";
        

echo "<h2 class='center m50em'>$title</h2>\n";

echo html_comment($sql);

echo "
<div id='divsearch' class='center em80'>
<form id='searchform' name='searchform' method='POST' 
                            onClick='eSetSort(event)' action=''>
<input type='hidden' name='sort' id='sort' value='{$sort}'>
<input type='hidden' name='desc' id='desc' value='{$desc}'>
<div>
<table class='dptable'>
<tr>
    <th>&nbsp;</th>
    <th>&nbsp;</th>
    <th>Language</th>
    <th>Genre</th>
    <th>PM</th>
    <th>Task</th>
    <th>Round</th>    
</tr>
<tr>
    <td rowspan='4'>
        <input id='dosearch' name='dosearch' type='submit'/>
    </td>
    <td>
        Title
        <input id='qtitle' name='qtitle' type='text'
        value='$qtitle' size='30' class='searchctl'>
    </td>


    <td rowspan='4'>
            $language_picker
    </td>
    <td rowspan='4'>
        <select id='qgenre[]' name='qgenre[]'
            multiple='multiple' size='8'
                class='searchctl'>\n";

        $optgenres = $Context->ActiveGenreArray();
        echo array_to_options($optgenres, true, $qgenre);
        echo "
        </select>
    </td>
    <td rowspan='4'>
        <select id='qpm[]' name='qpm[]'
            multiple='multiple' size='8'
                class='searchctl'>\n";

            $optpms = $Context->ActivePMArray();
            echo array_to_options($optpms, true, $qpm);
            echo "
        </select>
    </td>
    <td rowspan='4'>
        <select id='qtask[]' name='qtask[]'
            multiple='multiple' size='8'
            class='searchctl'>\n";

            $opttasks = $site_difficulties;
            echo array_to_options($opttasks, true, $qtask);

            echo "
        </select>
    </td>

    <td rowspan='4'>
        <select id='qroundid[]' name='qroundid[]'
           multiple='multiple' size='8'
            class='searchctl'>\n";

            $optroundids = RoundIdsInOrder();
            echo array_to_options($optroundids, true, $qroundid);

            echo "
        </select>
    </td>

</tr>

<tr>
    <td>
        Author
        <input id='qauthor' name='qauthor' size='30'
        type='text' class='searchctl' value='$qauthor'> 
    </td>
</tr>

<tr>
    <td>
    Status:
        <input type='radio' name='qstatus'  value='avail' "
        .($qstatus == "avail" ?  " checked='checked' " : "") .">
        Available
        <input type='radio' name='qstatus' value='unavail' "
        .($qstatus == "unavail" ?  " checked='checked' " : "") .">
        Unavailable
        <input type='radio' name='qstatus' value='both' "
        .($qstatus == "both" ?  " checked='checked' " : "") .">
        Both
    </td>
</tr>
<tr>
    <td>
        Project ID(s)
        <input id='qprojectid' name='qprojectid' size='40'
            type='text' class='searchctl' value='$qprojectid'> 
    </td>
</tr>
</table>
</div>

<div class='w70 left search-instructions'>
    <ul class='m25em'>
    " ._("<li>For titles and authors, matching uses
        wildcarding; 'ford' matches 'Oxford' and 'Stanford'.</li>

        <li>Search for multiple Project ids (with wildcarding) 
        by entering a list separated by spaces.</li>
        
        <li> Task, language, etc. allow multiple choices 
        by pressing the control-key.</li>")."
    </ul>
</div>
<!-- 
    $sql
-->
\n";


if ( $nprojects == 0 )
{
    echo _("<b>No projects matched the search criteria.</b>");
    dp_footer();
    exit;
}


// $min_row = ($rownum - 1) * $pagerows;
// $rows = array_slice($all_rows, $min_row, $rows_per_page, true);

$tbl->SetRows($all_rows);

echo "<div class='center'>\n";
results_navigator($rownum, $pagerows, $nprojects);
$tbl->EchoTable();
results_navigator($rownum, $pagerows, $nprojects);
echo "</div>\n";



echo "
</form>
</div>
<br />\n";
dp_footer();
exit;


function results_navigator($pagenum, $rows_per_page, $rowcount) {
    if ( $pagenum > 1 ) {
        $prevpage = $pagenum - 1;
        $url = unamp(ThisPageUrl() . "?pagenum={$prevpage}");
        echo "<a href='$url'>"._("Previous")."</a> |\n";
    }

    $minrow = $rows_per_page * ($pagenum-1) + 1;
    $maxrow = min($rows_per_page * $pagenum, $rowcount);

    $position = sprintf(
        _("Projects %1\$d to %2\$d of %3\$d\n"),
                                $minrow, $maxrow, $rowcount);
    echo "
    <span class='lfloat'>
        $position
    </span>\n";

    if ( $maxrow < $rowcount) {
        $nextpage = $pagenum + 1;
        $url = unamp(ThisPageUrl() . "?pagenum={$nextpage}");
        echo " | <a href='$url'>"._("Next")."</a>";
    }
}


function is_available($row) {
    return preg_match("/_avail/", $row['state'])
        ? "avail" 
        : "";
}

function difficulty_cell($row) {
    switch($row['difficulty'])
    {
//        case "easy":
//        case "hard":
        case "test":
        case "proof":
        case "reproof":
        case "markup":
        case "check":
        case "special":
        case "complete":
        case "undefined":
            return $row['difficulty'];
            break;
        default:
            return "undefined";
    }
}

function all_langs($row) {
    return $row['language']
        . ( empty($row['other_languages'])
            ? ""
            : "/". $row['other_languages']);
}

function roundid($row) {
    return preg_match("/^(.+?)\..*_(.*)$/", $row['state'], $matches) > 0
        ? $matches[1]."&nbsp;".$matches[2]
        : "";
}

function status($row) {
    return preg_match("/_(.*)$/", $row['state'], $matches) > 0
        ? $matches[1]
        : "";
}


function array_to_options($optarray, $is_blank = true, 
                            $selected = null) {
    $ret = $is_blank
        ? "<option value=''> </option>\n"
        : "";
    foreach($optarray as $opt => $val)
    {
        $ret .= ("<option value='$val'"
                . (is_array($selected)
                    ? (in_array($val, $selected)
                        ? " selected='selected'"
                        : "")
                    : (is_string($selected)
                        && $selected == $val
                        ? " selected='selected'"
                        : "")
                    ) .">$val</option>\n");
    }
    return $ret;
}

function project_search_view_sql($where, $orderby = "") {
    $where = trim($where);
    if(! empty($where))
    {
        $where = "WHERE \n".$where;
    }
    return  "SELECT
                p.projectid,
                p.nameofwork AS title,
                p.authorsname AS author,
                p.language,
                p.other_languages,
                p.difficulty,
                p.genre,
                p.state,
                p.sequence_code,
                p.username as pm,
                SUM( 
                    CASE WHEN pg.state LIKE '%".PAGE_AVAIL."'
                        THEN 1 ELSE 0 END
                ) AS pages_avail,
                SUM( 
                    CASE WHEN pg.state LIKE '%".PAGE_OUT."'
                        THEN 1 ELSE 0 END
                    ) AS pages_out,
                SUM( 
                    CASE WHEN pg.state LIKE '%".PAGE_TEMP."'
                        THEN 1 ELSE 0 END
                    ) AS pages_temp,
                SUM( 
                    CASE WHEN pg.state LIKE '%".PAGE_SAVED."'
                        THEN 1 ELSE 0 END
                    ) AS pages_saved,
                COUNT(pg.projectid) AS pages_total,
                SUM( 
                    CASE WHEN pg.state LIKE '%".PAGE_BAD."'
                        THEN 1 ELSE 0 END
                    ) AS pages_bad,
                COUNT(pg.fileid) AS pages_total,
                p.username AS project_manager,
                p.checkedoutby,
                pm.bb_id AS pm_bbid,
                CASE WHEN p.state LIKE 'P1%' THEN 1
                     WHEN p.state LIKE 'P2a%' THEN 2
                     WHEN p.state LIKE 'P2b%' THEN 3
                     WHEN p.state LIKE 'P2c%' THEN 4
                     WHEN p.state LIKE 'P3%'  THEN 5
                     WHEN p.state LIKE 'F1a%' THEN 6
                     WHEN p.state LIKE 'F1b%' THEN 7
                     WHEN p.state LIKE 'F1c%' THEN 8
                     WHEN p.state LIKE 'F2a%' THEN 9
                     WHEN p.state LIKE 'F2b%' THEN 10
                     ELSE 11
                END AS round_index,
                cob.bb_id AS cob_bbid
            FROM
                projects AS p
            LEFT JOIN
                pages AS pg ON p.projectid = pg.projectid
            LEFT JOIN
                users AS pm ON p.username = pm.username
            LEFT JOIN
                users as cob ON p.checkedoutby = cob.username
            $where
            GROUP BY
                p.projectid
            $orderby";
}

function title_link($title, $row) {
    global $code_url;
    $url = "$code_url/project.php"
        ."?projectid={$row['projectid']}'";
    return "<a href='{$url}>{$title}</a>";
}

function page_counts($row) {
    global $pm_url;
    $url = "$pm_url/page_detail.php"
            ."?projectid={$row['projectid']}";
            
    $fmt = "<a href='{$url}'>%d-%d-%d<br />(%d-%d)</a>";
    // if($row['pages_avail'] == 0)
    // {
        $fmt = "<span style='color: pink'>$fmt</span>";
    // }
    return sprintf($fmt, $row['pages_avail'],
         $row['pages_out'] + $row['pages_temp'],
         $row['pages_saved'], $row['pages_bad'],
         $row['pages_total']);
}

function pmlink($pm, $row) {
    return link_to_private_message($pm, $pm);
}

function coblink($cob, $row) {
    return empty($cob)
        ? ""
        : link_to_private_message($cob, $cob);
}

function words_link($row) {
    $projectid = $row['projectid'];
    return link_to_project_words($projectid, "Words", true);
}

function edit_link($row) {
    global $User;

    return ( $User->IsSiteManager()
             || $row['pm'] == $User->Username())
        ? link_to_edit_project($row['projectid'], "Edit", true)
        : "";
}
?>
